/**

	EXEC SP_BACKLOG_REPORT_PROC 12
*/
CREATE  PROC SP_BACKLOG_CHART_REPORT_PROC @NO_OF_MONTHS INT
 AS
 BEGIN
	DECLARE @RESULT_TABLE TABLE (		
			MONTH_YEAR VARCHAR(20),
			MONTH INT,
			YEAR INT,
            COOL_QTY INT,
            SHIPPED_QTY INT,
			BACKLOG_QTY	INT,
			BACKLOG_PRICE FLOAT,
			BACKLOG_AMOUNT NUMERIC(10,3)
			)

	DECLARE @MONTH_COUNT 	INT
	DECLARE @MONTH 		INT
	DECLARE @YEAR 		INT
    DECLARE @COOL_QTY INT
    DECLARE @SHIPPED_QTY INT
	DECLARE @BACKLOG_QTY INT
	DECLARE @BACKLOG_PRICE FLOAT
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	DECLARE @SHOW_NEXT INT
	DECLARE @SHORT_MONTH_NAME VARCHAR(10)

	SET @MONTH	= MONTH(GETDATE())-1 --zero based
	SET @YEAR	= YEAR(GETDATE())

	SET @MONTH_COUNT= 0
	SET @SHOW_NEXT = @NO_OF_MONTHS
	WHILE ( @MONTH_COUNT < @SHOW_NEXT )
	 BEGIN
		IF ( @MONTH > 11 ) --going forward
		 BEGIN
			SET @MONTH = 0
			SET @YEAR  = @YEAR + 1
		 END
		
		SET @START_DATE = CONVERT(DATETIME, CONVERT( VARCHAR, @MONTH+1 )+'/01/'+ CONVERT( VARCHAR, @YEAR ))
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )

		IF (@MONTH_COUNT = 0 )
		 BEGIN
			SELECT @START_DATE = MIN(COOL_REQUESTED_DATE) FROM CUSTOMER_OPEN_ORDER_LINEITEM
		 END

		PRINT @START_DATE
		PRINT @END_DATE

        SET @SHIPPED_QTY = 0
		SET @COOL_QTY = 0
		SET @BACKLOG_PRICE = 0

		SELECT @COOL_QTY = ISNULL(SUM(COOL.COOL_QTY),0), @BACKLOG_PRICE = ISNULL(AVG(COOL_UNIT_COST),0),
            @SHIPPED_QTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED),0)
          FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL
            LEFT OUTER JOIN CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT ON CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT.CSLIT_STATUS = 'SHIPPED'
				WHERE COOL_REQUESTED_DATE >= @START_DATE
					AND COOL_REQUESTED_DATE <= @END_DATE
		
		SET @SHORT_MONTH_NAME = left( datename( month,@END_DATE), 3 )

		INSERT INTO @RESULT_TABLE (
								MONTH_YEAR,
								MONTH,
								YEAR,
                                COOL_QTY,
                                SHIPPED_QTY,
								BACKLOG_QTY,
								BACKLOG_PRICE,
								BACKLOG_AMOUNT
                              )
			VALUES ( @SHORT_MONTH_NAME + '-' +  convert(varchar,@YEAR),
					@MONTH,
					@YEAR,
                    @COOL_QTY,
                    @SHIPPED_QTY,
					(@COOL_QTY-@SHIPPED_QTY),
					@BACKLOG_PRICE,
					((@COOL_QTY-@SHIPPED_QTY)*@BACKLOG_PRICE*0.1)
                  )

		SET @MONTH = @MONTH + 1
		SET @MONTH_COUNT = @MONTH_COUNT + 1
	 END

	SELECT * FROM @RESULT_TABLE	ORDER BY YEAR, MONTH
 END
